package cn.com.xjnt.vas.web.ordering.api;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.http.MediaType;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.alibaba.fastjson.JSONObject;
import com.frameworks.core.web.controller.BaseController;
import com.frameworks.core.web.result.DataResult;
import com.google.common.collect.Lists;

@Controller
@RequestMapping("/api/gh")
public class ClientAPI extends BaseController {
	private static String driverName = "net.sourceforge.jtds.jdbc.Driver";
	private static String url = "jdbc:jtds:sqlserver://10.104.3.161;databaseName=hospitaltest";
//	private static String url = "jdbc:jtds:sqlserver://10.104.3.161;databaseName=hospital";
	private static String user = "sa";
	private static String password = "";

	/**
	 * 查询挂号排班视图
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/findDoctor", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult findDoctor(@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		String deptCode = json.getString("deptCode");
		String sql = "select * from VIEW_REGIST_SCHEDULES_MARK v where v.DEPT_CODE = '"+deptCode+"'";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 挂号
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/gh", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult gh(@RequestBody JSONObject json){
		DataResult dataRet = new DataResult();
		
		String appHospitalMark = json.getString("APP_HOSPITAL_MARK");//医院标示
		String appPatientId = "";//病人id
		String appHid = "";//号源id
		String appDeptCode = json.getString("APP_DEPT_CODE");//科室编号
		String appDeptName = json.getString("APP_DEPT_NAME");//科室名称
		String appDoctorCode = json.getString("APP_DOCTOR_CODE");//医生编号
		String appDoctorName = json.getString("APP_DOCTOR_NAME");//医生姓名
		String appRegDate = json.getString("APP_REG_DATE");//挂号号源日期
		String appHbTime = json.getString("APP_HB_TIME");//号源时段
		String appClinicDuration = json.getString("APP_CLINIC_DURATION");//号源午别
		String appOperateType = "0";//操作类型
		String appSumFee = json.getString("APP_SUM_FEE");//挂号费
		String appPayWay = json.getString("APP_PAY_WAY");//支付方式
		String appThreeTradeNo = json.getString("APP_THREE_TRADE_NO");//支付订单号
		String appTradeOrderNo = "0";//趣医订单号
		String appDiagFee = "1.00";//诊疗费
		String appPharmacyFee = "1.00";//药事费
		
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "{call PROC_APP_REGIST(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
		try {
			con = getConnection();
			CallableStatement pstmt = con.prepareCall(sql);
//			PreparedStatement pstmt = con.prepareStatement(sql);\
			pstmt.setString("@APP_HOSPITAL_MARK", appHospitalMark);
			pstmt.setString("@APP_PATIENT_ID",appPatientId);
			pstmt.setString("@APP_HID",appHid);
			pstmt.setString("@APP_DEPT_CODE",appDeptCode);
			pstmt.setString("@APP_DEPT_NAME",appDeptName);
			pstmt.setString("@APP_DOCTOR_CODE",appDoctorCode);
			pstmt.setString("@APP_DOCTOR_NAME",appDoctorName);
			pstmt.setString("@APP_REG_DATE",appRegDate);
			pstmt.setString("@APP_HB_TIME",appHbTime);
			pstmt.setString("@APP_CLINIC_DURATION",appClinicDuration);
			pstmt.setString("@APP_OPERATE_TYPE",appOperateType);
			pstmt.setString("@APP_SUM_FEE",appSumFee);
			pstmt.setString("@APP_PAY_WAY",appPayWay);
			pstmt.setString("@APP_THREE_TRADE_NO",appThreeTradeNo);
			pstmt.setString("@APP_TRADE_ORDER_NO",appTradeOrderNo);
			pstmt.setString("@APP_DIAG_FEE",appDiagFee);
			pstmt.setString("@APP_PHARMACY_FEE",appPharmacyFee);
			
			pstmt.setString("@APP_ID_NO","");
			
			rs = pstmt.executeQuery();
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("预约挂号失败");
			logger.error("预约挂号失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 排队
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/pd", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult pd(@RequestBody JSONObject json){
		DataResult dataRet = new DataResult();
		String brid = json.getString("brId");//病人id
		String brxm = json.getString("brxm");//病人id
		String ksid = json.getString("ksid");//科室id
		String ksmc = json.getString("ksmc");//科室名称
		String ysid = json.getString("ysid");//医生id
		String ysxm = json.getString("ysxm");//医生姓名
		String type = "1";//状态
		
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "{call insert_pd_dpd_app(?,?,?,?,?,?,?)}";
		try {
			con = getConnection();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1,brid);
			pstmt.setString(2,brxm);
			pstmt.setString(3,ksid);
			pstmt.setString(4,ksmc);
			pstmt.setString(5,ysid);
			pstmt.setString(6,ysxm);
			pstmt.setString(7,type);
			
			rs = pstmt.executeQuery();
			
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("写入排队失败");
			logger.error("写入排队异常", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 查询挂号预约订单状态
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryPatsRegistStatus", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryPatsRegistStatus(@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
//		String sql = "select * from VIEW_APP_VISIT_STATUS v where v.PATIENT_ID = "+patientId+"";
		String sql = "select top 10 * from VIEW_APP_VISIT_STATUS v";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 查询挂号预约记录
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryAppointRegistRecords", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryAppointRegistRecords(@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
		String sql = "select * from VIEW_APP_APPOINT_REGIST_RECORDS v where v.PATIENT_ID = "+patientId+"";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 查询在院患者住院费用概要
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryHospFeeMaster", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryHospFeeMaster(@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
		String sql = "select * from VIEW_APP_IN_HOSPITAL v where v.PATIENT_ID = "+patientId+"";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	/**
	 * 查询在院患者费用记录详细
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryHospFeeInfo", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryHospFeeInfo (@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
		String sql = "select * from VIEW_APP_IN_HOSPITAL_INF v where v.PATIENT_ID =  "+patientId+"";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 查询病人住院费用信息
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryPrefeeMaster", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryPrefeeMaster (@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
		String sql = "select * from VIEW_APP_IN_HOSPITAL_PREFEE_MASTER v where v.PATIENT_ID = "+patientId+"";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 住院费用预交金充值记录查询
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryPrefeeInfo", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryPrefeeInfo (@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
		String sql = "select * from VIEW_APP_INHOSPITAL_PREFEE_RECHAGE v where v.PATIENT_ID = "+patientId+"";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 查取门诊费用主表
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryOutpfeeMaster", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryOutpfeeMaster (@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
		String sql = "select * from VIEW_APP_OUTPFEE_MASTER v where v.PATIENT_ID = "+patientId+"";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 查取门诊费用明细表
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryOutpfeeDetail", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryOutpfeeDetail (@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
		String sql = "select * from VIEW_APP_OUTPFEE_DETAIL v where v.PATIENT_ID = "+patientId+"";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 查取住院结算主表
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryInpFeeMaster", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryInpFeeMaster (@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
		String sql = "select * from VIEW_APP_INPFEE_MASTER v where v.PATIENT_ID = "+patientId+"";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 查取住院结算明细表
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryInpFeeDetail", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryInpFeeDetail (@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
		String sql = "select * from VIEW_APP_INPFEE_DETAIL v where v.PATIENT_ID = "+patientId+"";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 查取查取住院诊断表
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryInpFeeDiagnosis", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryInpFeeDiagnosis (@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
		String sql = "select * from VIEW_APP_INPFEE_DIAGNOSIS v where v.PATIENT_ID = "+patientId+"";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}
	
	/**
	 * 门诊缴费记录查询
	 * 
	 * @param json
	 * @return
	 */
	@RequestMapping(value = "/queryOutpFeeRecords", method = { RequestMethod.POST }, produces = {
			MediaType.APPLICATION_JSON_VALUE })
	@ResponseBody
	public DataResult queryOutpFeeRecords (@RequestBody JSONObject json) {
		DataResult dataRet = new DataResult();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String patientId = json.getString("patientId");
		String sql = "select * from VIEW_APP_OUTPFEE_RECORDS v where v.PATIENT_ID = "+patientId+"";
		try {
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);// 执行sql
			List<Object> lists = Lists.newArrayList();
			lists = resultSetToList(rs);
			dataRet.setRows(lists);
			dataRet.pushOk("操作成功");
		} catch (Exception e) {
			dataRet.pushError("查询失败");
			logger.error("查询相关数据失败", e);
		} finally {
			close(rs, stmt, con);
		}
		return dataRet;
	}

		
	
	
//-------------------------------------------------------工具方法-------------------------------------------------------------------
	/**
	 * 获得连接
	 * 
	 * @return
	 */
	public Connection getConnection() {
		try {
			Class.forName(driverName);
			return DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 关闭相关连接
	 * 
	 * @param rs
	 * @param stmt
	 * @param con
	 */
	public void close(ResultSet rs, Statement stmt, Connection con) {
		if (rs != null) {
			try {
				rs.close();
			} catch (Exception e) {
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (Exception e) {
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (Exception e) {
			}
		}
	}

	//结果集转list
	public static List<Object> resultSetToList(ResultSet rs) throws java.sql.SQLException {
		if (rs == null){
			return Collections.emptyList();
		}
		ResultSetMetaData md = rs.getMetaData(); // 得到结果集(rs)的结构信息，比如字段数、字段名等
		int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
		List<Object> list = new ArrayList<Object>();
		Map<String, Object> rowData = new HashMap<String, Object>();
		while (rs.next()) {
			rowData = new HashMap<String, Object>(columnCount);
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));
			}
			list.add(rowData);
			System.out.println("list:" + list.toString());
		}
		return list;
	}
}
